package com.cinema.dao;

import com.cinema.entity.*;
import com.cinema.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 订单数据访问对象
 */
public class OrderDAO {

    /**
     * 创建订单
     */
    public Integer createOrder(Order order, List<Integer> seatIds) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Integer orderId = null;

        try {
            conn = DBUtil.getConnection();
            DBUtil.beginTransaction(conn);

            // 插入订单
            String sql = "INSERT INTO orders (order_no, user_id, showtime_id, total_amount, " +
                        "ticket_count, status, expire_time) VALUES (?, ?, ?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, order.getOrderNo());
            pstmt.setInt(2, order.getUserId());
            pstmt.setInt(3, order.getShowtimeId());
            pstmt.setBigDecimal(4, order.getTotalAmount());
            pstmt.setInt(5, order.getTicketCount());
            pstmt.setString(6, order.getStatus());
            pstmt.setTimestamp(7, new Timestamp(order.getExpireTime().getTime()));

            int rows = pstmt.executeUpdate();
            if (rows > 0) {
                rs = pstmt.getGeneratedKeys();
                if (rs.next()) {
                    orderId = rs.getInt(1);
                }
            }

            // 插入订单座位
            if (orderId != null && seatIds != null && !seatIds.isEmpty()) {
                pstmt.close();
                String seatSql = "INSERT INTO order_seats (order_id, seat_id, price, status) VALUES (?, ?, ?, ?)";
                pstmt = conn.prepareStatement(seatSql);

                for (Integer seatId : seatIds) {
                    pstmt.setInt(1, orderId);
                    pstmt.setInt(2, seatId);
                    pstmt.setBigDecimal(3, order.getTotalAmount().divide(java.math.BigDecimal.valueOf(seatIds.size())));
                    pstmt.setString(4, "BOOKED");
                    pstmt.addBatch();
                }

                pstmt.executeBatch();
            }

            DBUtil.commitTransaction(conn);
        } catch (SQLException e) {
            e.printStackTrace();
            DBUtil.rollbackTransaction(conn);
            orderId = null;
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return orderId;
    }

    /**
     * 根据用户ID获取订单列表
     */
    public List<Order> findByUserId(Integer userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Order> orders = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT o.*, m.id as movie_id, m.title as movie_title, m.poster_url, " +
                        "c.id as cinema_id, c.name as cinema_name, c.address as cinema_address, " +
                        "h.id as hall_id, h.name as hall_name, " +
                        "s.show_date, s.show_time, s.price " +
                        "FROM orders o " +
                        "LEFT JOIN showtimes s ON o.showtime_id = s.id " +
                        "LEFT JOIN movies m ON s.movie_id = m.id " +
                        "LEFT JOIN cinemas c ON s.cinema_id = c.id " +
                        "LEFT JOIN halls h ON s.hall_id = h.id " +
                        "WHERE o.user_id = ? " +
                        "ORDER BY o.created_at DESC";

            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Order order = mapResultSetToOrder(rs);

                // 设置关联的场次信息
                Showtime showtime = new Showtime();
                showtime.setId(rs.getInt("showtime_id"));
                showtime.setMovieId(rs.getInt("movie_id"));
                showtime.setCinemaId(rs.getInt("cinema_id"));
                showtime.setHallId(rs.getInt("hall_id"));
                showtime.setShowDate(rs.getDate("show_date"));
                showtime.setShowTime(rs.getTimestamp("show_time"));
                showtime.setPrice(rs.getBigDecimal("price"));

                Movie movie = new Movie();
                movie.setId(rs.getInt("movie_id"));
                movie.setTitle(rs.getString("movie_title"));
                movie.setPosterUrl(rs.getString("poster_url"));
                showtime.setMovie(movie);

                Cinema cinema = new Cinema();
                cinema.setId(rs.getInt("cinema_id"));
                cinema.setName(rs.getString("cinema_name"));
                cinema.setAddress(rs.getString("cinema_address"));
                showtime.setCinema(cinema);

                Hall hall = new Hall();
                hall.setId(rs.getInt("hall_id"));
                hall.setName(rs.getString("hall_name"));
                showtime.setHall(hall);

                order.setShowtime(showtime);
                orders.add(order);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return orders;
    }

    /**
     * 根据订单号获取订单
     */
    public Order findByOrderNo(String orderNo) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Order order = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT o.*, m.id as movie_id, m.title as movie_title, m.poster_url, " +
                        "c.id as cinema_id, c.name as cinema_name, c.address as cinema_address, " +
                        "h.id as hall_id, h.name as hall_name, " +
                        "s.show_date, s.show_time, s.price, s.available_seats, s.total_seats " +
                        "FROM orders o " +
                        "LEFT JOIN showtimes s ON o.showtime_id = s.id " +
                        "LEFT JOIN movies m ON s.movie_id = m.id " +
                        "LEFT JOIN cinemas c ON s.cinema_id = c.id " +
                        "LEFT JOIN halls h ON s.hall_id = h.id " +
                        "WHERE o.order_no = ?";

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, orderNo);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                order = mapResultSetToOrder(rs);

                // 设置关联的场次信息
                Showtime showtime = new Showtime();
                showtime.setId(rs.getInt("showtime_id"));
                showtime.setMovieId(rs.getInt("movie_id"));
                showtime.setCinemaId(rs.getInt("cinema_id"));
                showtime.setHallId(rs.getInt("hall_id"));
                showtime.setShowDate(rs.getDate("show_date"));
                showtime.setShowTime(rs.getTimestamp("show_time"));
                showtime.setPrice(rs.getBigDecimal("price"));
                showtime.setAvailableSeats(rs.getInt("available_seats"));
                showtime.setTotalSeats(rs.getInt("total_seats"));

                Movie movie = new Movie();
                movie.setId(rs.getInt("movie_id"));
                movie.setTitle(rs.getString("movie_title"));
                movie.setPosterUrl(rs.getString("poster_url"));
                showtime.setMovie(movie);

                Cinema cinema = new Cinema();
                cinema.setId(rs.getInt("cinema_id"));
                cinema.setName(rs.getString("cinema_name"));
                cinema.setAddress(rs.getString("cinema_address"));
                showtime.setCinema(cinema);

                Hall hall = new Hall();
                hall.setId(rs.getInt("hall_id"));
                hall.setName(rs.getString("hall_name"));
                showtime.setHall(hall);

                order.setShowtime(showtime);

                // 获取订单座位信息
                order.setOrderSeats(getOrderSeats(order.getId()));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return order;
    }

    /**
     * 更新订单状态
     */
    public boolean updateOrderStatus(Integer orderId, String status) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE orders SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, status);
            pstmt.setInt(2, orderId);

            int rows = pstmt.executeUpdate();
            success = rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt);
        }

        return success;
    }

    /**
     * 支付订单
     */
    public boolean payOrder(String orderNo, String paymentMethod) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE orders SET status = 'PAID', payment_method = ?, " +
                        "payment_time = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP " +
                        "WHERE order_no = ? AND status = 'PENDING'";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, paymentMethod);
            pstmt.setString(2, orderNo);

            int rows = pstmt.executeUpdate();
            success = rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt);
        }

        return success;
    }

    /**
     * 取消过期订单
     */
    public int cancelExpiredOrders() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int cancelledCount = 0;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE orders SET status = 'EXPIRED', updated_at = CURRENT_TIMESTAMP " +
                        "WHERE status = 'PENDING' AND expire_time < CURRENT_TIMESTAMP";
            pstmt = conn.prepareStatement(sql);

            cancelledCount = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt);
        }

        return cancelledCount;
    }

    /**
     * 更新订单场次和金额（改票用）
     */
    public boolean updateOrderShowtime(Integer orderId, Integer newShowtimeId, java.math.BigDecimal newTotalAmount) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE orders SET showtime_id = ?, total_amount = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, newShowtimeId);
            pstmt.setBigDecimal(2, newTotalAmount);
            pstmt.setInt(3, orderId);

            int rows = pstmt.executeUpdate();
            success = rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt);
        }

        return success;
    }

    /**
     * 获取订单座位信息
     */
    private List<OrderSeat> getOrderSeats(Integer orderId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<OrderSeat> orderSeats = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT os.*, s.row_num, s.seat_num, s.seat_type " +
                        "FROM order_seats os " +
                        "LEFT JOIN seats s ON os.seat_id = s.id " +
                        "WHERE os.order_id = ?";

            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, orderId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                OrderSeat orderSeat = new OrderSeat();
                orderSeat.setId(rs.getInt("id"));
                orderSeat.setOrderId(rs.getInt("order_id"));
                orderSeat.setSeatId(rs.getInt("seat_id"));
                orderSeat.setPrice(rs.getBigDecimal("price"));
                orderSeat.setStatus(rs.getString("status"));

                Seat seat = new Seat();
                seat.setId(rs.getInt("seat_id"));
                seat.setRowNum(rs.getString("row_num"));
                seat.setSeatNum(rs.getInt("seat_num"));
                seat.setSeatType(rs.getString("seat_type"));
                orderSeat.setSeat(seat);

                orderSeats.add(orderSeat);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return orderSeats;
    }

    /**
     * 将ResultSet映射为Order对象
     */
    private Order mapResultSetToOrder(ResultSet rs) throws SQLException {
        Order order = new Order();
        order.setId(rs.getInt("id"));
        order.setOrderNo(rs.getString("order_no"));
        order.setUserId(rs.getInt("user_id"));
        order.setShowtimeId(rs.getInt("showtime_id"));
        order.setTotalAmount(rs.getBigDecimal("total_amount"));
        order.setTicketCount(rs.getInt("ticket_count"));
        order.setStatus(rs.getString("status"));
        order.setPaymentMethod(rs.getString("payment_method"));
        order.setPaymentTime(rs.getTimestamp("payment_time"));
        order.setExpireTime(rs.getTimestamp("expire_time"));
        order.setCreatedAt(rs.getTimestamp("created_at"));
        order.setUpdatedAt(rs.getTimestamp("updated_at"));
        return order;
    }
}
